﻿using System;
using System.Globalization;
using DGen;
using System.Text.RegularExpressions;

namespace Tab2Sql
{
    /// <summary>
    /// class for datatype handling.
    /// </summary>
    public static class DTypes
    {
        /// <summary>
        ///
        /// </summary>
        /// <param name="dataType">
        /// A <see cref="DataTypes"/>
        /// </param>
        /// <returns>
        /// A <see cref="DGen.DataTypes"/>
        /// </returns>
        public static DGen.DataTypes translateDTypes(DataTypes dataType)
        {
            switch (dataType)
            {
                case DataTypes.unknown:
                    return DGen.DataTypes.unknown;

                case DataTypes.NULL:
                    return DGen.DataTypes.NULL;

                case DataTypes.datetime:
                    return DGen.DataTypes.datetime;

                case DataTypes.integer:
                    return DGen.DataTypes.integer;
                case DataTypes.bigint:
                    return DGen.DataTypes.bigint;
                case DataTypes.numericScientific:
                    return DGen.DataTypes.numericScientific;
                case DataTypes.numeric:
                    return DGen.DataTypes.numeric;
                case DataTypes.percentage:
                    return DGen.DataTypes.percentage;
                case DataTypes.varchar:
                    return DGen.DataTypes.varchar;
                default:
                    return DGen.DataTypes.varchar;
            }
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="dataTypes">
        /// A <see cref="DataTypes"/>
        /// </param>
        /// <returns>
        /// A <see cref="Type"/>
        /// </returns>
        public static Type translateSqlType(DataTypes dataTypes)
        {
            switch (dataTypes)
            {
                case DataTypes.unknown:
                    return typeof(System.String);
                case DataTypes.NULL:
                    return typeof(System.String);

                case DataTypes.datetime:
                    return typeof(System.DateTime);

                case DataTypes.integer:
                    return typeof(System.Int32);

                case DataTypes.bigint:
                    return typeof(System.Int64);
                case DataTypes.numericScientific:
                    return typeof(System.Double);
                case DataTypes.numeric:
                    return typeof(System.Double);
                case DataTypes.percentage:
                    return typeof(System.Double);

                case DataTypes.varchar:
                    return typeof(System.String);

                default:
                    return typeof(System.String);

            }

        }
/// <summary>
/// 
/// </summary>
/// <param name="dataTypes">
/// A <see cref="DataTypes"/>
/// </param>
/// <returns>
/// A <see cref="TypeCode"/>
/// </returns>
        public static TypeCode translateSqlType2TypeCode(DataTypes dataTypes)
        {
            switch (dataTypes)
            {
                case DataTypes.unknown:
                    return TypeCode.String;
                case DataTypes.NULL:
                    return TypeCode.DBNull;
                case DataTypes.datetime:
                    return TypeCode.DateTime;
                case DataTypes.integer:
                    return TypeCode.Int32;
                case DataTypes.bigint:
                    return TypeCode.Int64;
                case DataTypes.numeric:
                    return TypeCode.Double;
                case DataTypes.percentage:
                    return TypeCode.Double;
                case DataTypes.varchar:
                    return TypeCode.String;
                default:
                    return TypeCode.String;

            }
        }







        /// <summary>
        /// More sophisticated approach to "Is this a number"
        /// </summary>
        /// <param name="ObjectToTest"></param>
        /// <param name="advancedMode"></param>
        /// <returns></returns>
        public static NumericStatus IsNumeric(object ObjectToTest, bool advancedMode)
        {
            try
            {
                bool basicTest;
                bool scientificTest;
                bool hexTest;
                bool formatTest;
                bool intTest;
                bool floatTest;
                double OutValue;
                int OutValueInt;
                string TestCase = DText.RemoveWhiteSpace(ObjectToTest.ToString());
                if (ObjectToTest == null || TestCase.Length == 0)
                {
                    return NumericStatus.NotNumeric;
                }


                basicTest = double.TryParse(TestCase, NumberStyles.Any, CultureInfo.CurrentCulture, out OutValue);
                if (!basicTest) { return NumericStatus.NotNumeric; }

                hexTest = int.TryParse(TestCase, NumberStyles.HexNumber, CultureInfo.CurrentCulture, out OutValueInt);

                intTest = int.TryParse(TestCase, NumberStyles.Integer, CultureInfo.CurrentCulture, out OutValueInt);

                scientificTest = double.TryParse(TestCase, NumberStyles.Float | NumberStyles.AllowExponent, CultureInfo.CurrentCulture, out OutValue);

                floatTest = double.TryParse(TestCase, NumberStyles.Float, CultureInfo.CurrentCulture, out OutValue);

                formatTest = double.TryParse(TestCase, NumberStyles.Currency, CultureInfo.CurrentCulture, out OutValue);



                if (formatTest == true && floatTest == false) { return NumericStatus.FormattedNumber; }

                if (scientificTest == true && floatTest == false) { return NumericStatus.ScientificNotation; }

                if (intTest == false && hexTest == true) { return NumericStatus.HexDecimal; }

                if (intTest == false && floatTest == true) { return NumericStatus.FloatingPoint; }

                if (intTest) { return NumericStatus.WholeNumber; }

                if (intTest == false && floatTest == false && hexTest == false && scientificTest == false && formatTest == false)
                {
                    return NumericStatus.NotNumeric;
                }
                return NumericStatus.GeneralNumeric;
            }
            catch
            {
                return NumericStatus.NotNumeric;
            }
            


        }


        /// <summary>
        /// is this object numeric? Returns true or false.
        /// </summary>
        /// <param name="ObjectToTest"></param>
        /// <returns></returns>
        public static bool IsNumeric(object ObjectToTest)
        {
            // from : http://www.tangiblesoftwaresolutions.com/Articles/CSharp%20Equivalent%20to%20IsNumeric.htm
            if (ObjectToTest == null)
            {
                return false;
            }
            else
            {
                double OutValue;
                return double.TryParse(ObjectToTest.ToString().Trim().Replace(" ", String.Empty),
                                       NumberStyles.Any,
                                       CultureInfo.CurrentCulture,
                                       out OutValue);
            }
        }
        /// <summary>
        /// is this number an integer?
        /// </summary>
        /// <param name="d"></param>
        /// <returns></returns>
        public static bool IsInteger(double d)
        {
            bool IsIntegerValue = false;


            Double remainder = Math.IEEERemainder(d, 1);
            if (remainder == 0)
            {
                IsIntegerValue = true;
            }
            else
            {
                IsIntegerValue = false;
            }
            return IsIntegerValue;
        }
        /// <summary>
        /// is this string an integer?
        /// </summary>
        /// <param name="theValue"></param>
        /// <returns></returns>
        /// 
        public static bool IsStringPercentage(string theValue)
        {
            if (theValue.Contains("%"))
            {
                if (IsNumeric(theValue.Replace("%", String.Empty)))
                {
                    return true;
                }
            }
            return false;
        }
        /// <summary>
        /// Can this string be converted to a decimal?
        /// Relies on exception raising, so do not overuse.
        /// </summary>
        /// <param name="theValue"></param>
        /// <returns></returns>
        public static bool IsStringDecimal (string theValue)
        {
            try
            {
                Convert.ToDecimal (theValue);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
       /// Can this string be converted to an integer?
        /// Relies on exception raising, so do not overuse..
        /// </summary>
        /// <param name="theValue">
        /// A <see cref="System.String"/>
        /// </param>
        /// <returns>
        /// A <see cref="System.Boolean"/>
        /// </returns>
        public static bool IsStringInteger(string theValue)
        {
            try
            {
                Convert.ToInt32(theValue);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// Matches to see if fits any YYYY-MM-DD or DD-MM-YYYY.
        /// Allows for YYYY.MM.DD or DD/MM/YYYY or any reasonable combination.
        /// Does not check for nonsensical dates (e.g. 29th February 2003)
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public static bool IsDate(string input)
        {
            bool rtnAnswer = false;
            //    input = input.Trim();
            input = input.Replace('/', '-');
            input = input.Replace('.', '-');

            string SQLDateTime = @"(\d+)-(\d+)-(\d+)\s?(\d+):(\d+):(\d+)-(\d+)";
            // DD-MM-YYYY dates
            string DDMMYYYY =
                @"^([3][0-1]|[2][0-9]|[1][0-9]|[0][1-9])-([1][0-2]|[0][1-9]|[1-9])-([1][0-9]{3}|[2][0-9]{3})$";
            // MM-DD-YYYY dates
            string MMDDYYYY =
                @"^([1][0-2]|[0][1-9]|[1-9])-([3][0-1]|[2][0-9]|[1][0-9]|[0][1-9])-([1][0-9]{3}|[2][0-9]{3})$";
            // YYYY-MM-DD dates
            string YYYYMMDD =
                @"^([1][0-9]{3}|[2][0-9]{3})-([1][0-2]|[0][1-9]|[1-9])-([3][0-1]|[2][0-9]|[1][0-9]|[0][1-9])$";
            // YYYY-DD-MM dates
            string YYYYDDMM =
                @"^([1][0-9]{3}|[2][0-9]{3})-([3][0-1]|[2][0-9]|[1][0-9]|[0][1-9])-([1][0-2]|[0][1-9]|[1-9])$";


            rtnAnswer = isMatch(input, SQLDateTime);
            if (rtnAnswer == false)
            {
                rtnAnswer = isMatch(input, DDMMYYYY);
            }
            if (rtnAnswer == false)
            {
                rtnAnswer = isMatch(input, MMDDYYYY);
            }
            if (rtnAnswer == false)
            {
                rtnAnswer = isMatch(input, YYYYMMDD);
            }
            if (rtnAnswer == false)
            {
                rtnAnswer = isMatch(input, YYYYDDMM);
            }


            return rtnAnswer;
        }


        private static bool isMatch(string input, string p5)
        {
            Match m5 = Regex.Match(input, p5);
            if (m5.Success)
            {

                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// Returns the probable datatype of a given string value.
        /// </summary>
        /// <param name="colValue"></param>
        /// <returns></returns>
        public static DataTypes GuessMyType(string colValue)
        {
            DataTypes returnType = DataTypes.unknown;
            char[] badChars = { '£', '$' };
            colValue = colValue.Trim();
            if (colValue.Length == 0) { return DataTypes.NULL; }
            colValue = colValue.TrimStart(badChars);
            if (colValue.ToUpperInvariant() == "NULL") { return DataTypes.NULL; }
            if (colValue.Trim().Length > 30) { return DataTypes.varchar; }
            int letterCount = 0;
            for (int i = 0; i < Math.Min(colValue.Trim().Length, 30); i++)
            {
                if (Char.IsLetter(colValue[i]) == true) { letterCount++; }
                if (letterCount > 2) { return DataTypes.varchar; }
            }
            if (colValue.Length > 8)
            {
                if (colValue.Length == 23)
                {
                    if (IsDate(colValue) == true)
                    {
                        return DataTypes.datetime;
                    }
                }
                if ((colValue.Substring(0, 3) == "200" || colValue.Substring(0, 3) == "199") && (colValue.Substring(4, 1) == "-") && (colValue.Substring(7, 1) == "-"))
                {
                    if (IsDate(colValue) == true) { return DataTypes.datetime; }
                }
            }
            if (IsNumeric(colValue))
            {
                if (colValue.StartsWith("0") && !colValue.Contains(".") && colValue.Trim().Replace("0", String.Empty).Length > 0)
                {
                    /* This may seem like an odd choice, but this is to catch character strings which start with 0.
                     * this is a big issue for the environment I work in where our property references (unique identifiers) are often of 
                     * the form 099032100 and where the leading zero matters. So anything which starts with a zero, without a decimal
                     * point and which is not 0 or 00 or 000 or 0000 (etc) is classed as a varchar.
                     *                  - Darian Linehan
                     *                  22/07/2009 21:57:46
                     */
                    return DataTypes.varchar;
                }
                return whatKindOfNumber(colValue);

            }// END OF NUMERIC BIT;

            if (IsDate(colValue) == true)
            {
                return DataTypes.datetime;
            }
            if (IsStringPercentage(colValue) == true)
            {
                return DataTypes.percentage;
            }
            if (returnType == DataTypes.unknown)
            {
                return DataTypes.varchar;
            }
            return DataTypes.varchar;
        }

        private static DataTypes whatKindOfNumber(string colValue)
        {
            NumericStatus nStatus = IsNumeric(colValue, true);
            switch (nStatus)
            {
                case NumericStatus.WholeNumber:
                    double dx;
                    bool m = Double.TryParse(colValue, out dx);
                    if (m == false)
                    {
                        return DataTypes.varchar;
                    }
                    if (dx > 2147483000 || dx < -2147483000)
                    {
                        return DataTypes.bigint;
                    }
                    else
                    {
                        return DataTypes.integer;
                    }
                case NumericStatus.FormattedNumber:
                    colValue = DText.CleanNumeric(colValue);
                    NumericStatus tmp = IsNumeric(colValue, true);
                    if (tmp == NumericStatus.FormattedNumber)
                    {
                        string msg = string.Format("Fuck all that. Loop detected. Formatted number gubbins. Value that caused problem :[ {0} ]", colValue);
                        throw new Exception(msg);
                    }
                    else
                    {
                        return whatKindOfNumber(colValue);
                    }
                case NumericStatus.FloatingPoint:
                    return DataTypes.numeric;
                case NumericStatus.HexDecimal:
                    return DataTypes.varchar;
                case NumericStatus.ScientificNotation:
                    return DataTypes.numericScientific;
                case NumericStatus.NotNumeric:
                    colValue = DText.CleanNumeric(colValue);
                    NumericStatus tmp2 = IsNumeric(colValue, true);
                    if (tmp2 == NumericStatus.NotNumeric)
                    {
                        return DataTypes.varchar;
                    }
                    else
                    {
                        return whatKindOfNumber(colValue);
                    }
                default:
                    return DataTypes.numeric;

            }
        }
        /// <summary>
        /// PRECEDENCE OF DataTypes
        /// </summary>
        /// <param name="old1"></param>
        /// <param name="new1"></param>
        /// <returns></returns>
        public static DataTypes chooseDataType(DataTypes old1, DataTypes new1)
        {
            DataTypes WinningDataType = DataTypes.unknown;

            if (old1 > new1)
            {
                WinningDataType = old1;
            }
            else
            {
                WinningDataType = new1;
            }
            return WinningDataType;
        }

        /// <summary>
        /// When processing a column, we have to guess the datatype. 
        /// If we have a list as below then the datatype assumption will shift as we move along.
        ///       1,    2,    3,    4,    5.0,        6.1111,     Test123
        ///       INT   INT   INT   INT   FLOAT       FLOAT       VARCHAR
        /// 
        /// 
        /// </summary>
        /// <param name="old1"></param>
        /// <param name="new1"></param>
        public static void chooseDataType(DataTypes old1, ref DataTypes new1)
        {
            DataTypes WinningDataType = DataTypes.unknown;

            if (old1 > new1)
            {
                WinningDataType = old1;
            }
            else
            {
                WinningDataType = new1;
            }
            new1 = WinningDataType;
        }
    }
}